*------------------------------------------------------------------------------*
	*** CREATE REFORMED COUNTIES FOR PRE-REFORM PERIOD - POPULATION ***
*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern 2000
import excel "${rawdata}refromed_counties/population/A133G 2000 00.xls", sheet("Gemeinden 2000") clear
keep A B E AO-AX
drop in 1/3
drop if B==""
rename (A B E)(Gemeinde_ID Gemeinde pop_tot)
destring pop_tot AO-AX, replace
egen int pop_tot15_64 = rowtotal(AO-AX)
gen year=2000
drop AO-AX
order Gemeinde_ID Gemeinde year
save "${tempdata}pop_2000.dta", replace

*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern 2001
import excel "${rawdata}refromed_counties/population/A133G 2001 00.xls", sheet("Tabelle1") clear
keep A B C I-R
drop in 1/4
drop if A=="" | A=="13"
rename (A B C)(Gemeinde_ID Gemeinde pop_tot)
destring pop_tot I-R, replace
egen int pop_tot15_64 = rowtotal(I-R)
gen year=2001
drop I-R
order Gemeinde_ID Gemeinde year
save "${tempdata}pop_2001.dta", replace

*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern 2002
import excel "${rawdata}refromed_counties/population/A133G 2002 00.xls", sheet("Tabelle1") clear
keep A B C I-R
drop in 1/4
drop if A=="13" | A==""
drop in 980
rename (A B C)(Gemeinde_ID Gemeinde pop_tot)
destring pop_tot I-R, replace
egen int pop_tot15_64 = rowtotal(I-R)
gen year=2002
drop I-R
order Gemeinde_ID Gemeinde year
save "${tempdata}pop_2002.dta", replace

*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern 2003
import excel "${rawdata}refromed_counties/population/A133G 2003 00.xls", sheet("Gemeinden") clear
keep A B C I-R
drop in 1/5
drop if A=="13"
rename (A B C)(Gemeinde_ID Gemeinde pop_tot)
destring pop_tot I-R, replace
egen int pop_tot15_64 = rowtotal(I-R)
gen year=2003
drop I-R
order Gemeinde_ID Gemeinde year
save "${tempdata}pop_2003.dta", replace

*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern 2004
import excel "${rawdata}refromed_counties/population/A133G 2004 00.xls", sheet("Gemeinden") clear
keep A B C I-R
drop in 1/5
drop if A=="13"
rename (A B C)(Gemeinde_ID Gemeinde pop_tot)
destring pop_tot I-R, replace
egen int pop_tot15_64 = rowtotal(I-R)
gen year=2004
drop I-R
order Gemeinde_ID Gemeinde year
save "${tempdata}pop_2004.dta", replace

*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern 2005
import excel "${rawdata}refromed_counties/population/A133G 2005 00.xls", sheet("Gemeinden") clear
keep A B C I-R
drop in 1/5
drop if A=="" | A=="13"
rename (A B C)(Gemeinde_ID Gemeinde pop_tot)
destring pop_tot I-R, replace
egen int pop_tot15_64 = rowtotal(I-R)
gen year=2005
drop I-R
order Gemeinde_ID Gemeinde year
save "${tempdata}pop_2005.dta", replace

*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern 2006
import excel "${rawdata}refromed_counties/population/A133G 2006 00.xls", sheet("Gemeinden") clear
keep A B C I-R
drop in 1/4
drop if A=="" | A=="13"
rename (A B C)(Gemeinde_ID Gemeinde pop_tot)
destring pop_tot I-R, replace
egen int pop_tot15_64=rowtotal(I-R)
gen year=2006
drop I-R
order Gemeinde_ID Gemeinde year
save "${tempdata}pop_2006.dta", replace

*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern 2007
import excel "${rawdata}refromed_counties/population/A133G 2007 00.xls", sheet("Gemeinden") clear
keep A B C I-R
drop in 1/5
drop if A=="" | A=="13"
rename (A B C)(Gemeinde_ID Gemeinde pop_tot)
destring pop_tot I-R, replace
egen int pop_tot15_64 = rowtotal(I-R)
gen year=2007
drop I-R
order Gemeinde_ID Gemeinde year
save "${tempdata}pop_2007.dta", replace

*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern 2008
import excel "${rawdata}refromed_counties/population/A133G 2008 00.xls", sheet("Gemeinden") clear
keep A B C I-R
drop in 1/5
drop if A=="" | A=="13"
rename (A B C)(Gemeinde_ID Gemeinde pop_tot)
destring pop_tot I-R, replace
egen int pop_tot15_64 = rowtotal(I-R)
gen year=2008
drop I-R
order Gemeinde_ID Gemeinde year
save "${tempdata}pop_2008.dta", replace

*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern 2009
import excel "${rawdata}refromed_counties/population/A133G 2009 00.xls", sheet("Gemeinden") clear
keep A B C I-R
drop in 1/5
drop if A=="" | A=="13"
rename (A B C)(Gemeinde_ID Gemeinde pop_tot)
destring pop_tot I-R, replace
egen int pop_tot15_64 = rowtotal(I-R)
gen year=2009
drop I-R
order Gemeinde_ID Gemeinde year
save "${tempdata}pop_2009.dta", replace

*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern 2010
import excel "${rawdata}refromed_counties/population/A133G 2010 00.xls", sheet("Gemeinden") clear
keep A B C I-R
drop in 1/5
drop if A=="" | A=="13"
rename (A B C)(Gemeinde_ID Gemeinde pop_tot)
destring pop_tot I-R, replace
egen int pop_tot15_64 = rowtotal(I-R)
gen year=2010
drop I-R
order Gemeinde_ID Gemeinde year
save "${tempdata}pop_2010.dta", replace

*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern 2011
import excel "${rawdata}refromed_counties/population/A133G 2011 00.xls", sheet("1.1") clear
keep B C D H-Q
drop in 1/7
drop if B=="" | B=="13"
rename (B C D)(Gemeinde_ID Gemeinde pop_tot)
destring pop_tot H-Q, replace
egen int pop_tot15_64 = rowtotal(H-Q)
gen year=2011
drop H-Q
order Gemeinde_ID Gemeinde year
save "${tempdata}pop_2011.dta", replace

*------------------------------------------------------------------------------*

***Full dataset
clear 
forval y = 2000/2011 {
	append using "${tempdata}pop_`y'.dta"
	erase "${tempdata}pop_`y'.dta"
}

gen kreis_code=.
gen Kreis=.
tostring kreis_code Kreis, replace 
replace kreis_code = "13075" if substr(Gemeinde_ID,1,5) == "13001"
replace kreis_code = "13071" if substr(Gemeinde_ID,1,5) == "13002"
replace kreis_code = "13073" if substr(Gemeinde_ID,1,5) == "13005"
replace kreis_code = "13074" if substr(Gemeinde_ID,1,5) == "13006"
replace kreis_code = "13072" if substr(Gemeinde_ID,1,5) == "13051"
replace kreis_code = "13071" if substr(Gemeinde_ID,1,5) == "13052"
replace kreis_code = "13072" if substr(Gemeinde_ID,1,5) == "13053"
replace kreis_code = "13076" if substr(Gemeinde_ID,1,5) == "13054"
replace kreis_code = "13071" if substr(Gemeinde_ID,1,5) == "13055"
replace kreis_code = "13071" if substr(Gemeinde_ID,1,5) == "13056"
replace kreis_code = "13073" if substr(Gemeinde_ID,1,5) == "13057"
replace kreis_code = "13074" if substr(Gemeinde_ID,1,5) == "13058"
replace kreis_code = "13075" if substr(Gemeinde_ID,1,5) == "13059"
replace kreis_code = "13076" if substr(Gemeinde_ID,1,5) == "13060"
replace kreis_code = "13073" if substr(Gemeinde_ID,1,5) == "13061"
replace kreis_code = "13075" if substr(Gemeinde_ID,1,5) == "13062"
replace kreis_code = "13003" if substr(Gemeinde_ID,1,5) == "13003"
replace kreis_code = "13004" if substr(Gemeinde_ID,1,5) == "13004"
replace kreis_code = "13071" if substr(Gemeinde_ID,1,5) == "13071"
replace kreis_code = "13072" if substr(Gemeinde_ID,1,5) == "13072"
replace kreis_code = "13073" if substr(Gemeinde_ID,1,5) == "13073"
replace kreis_code = "13074" if substr(Gemeinde_ID,1,5) == "13074"
replace kreis_code = "13075" if substr(Gemeinde_ID,1,5) == "13075"
replace kreis_code = "13076" if substr(Gemeinde_ID,1,5) == "13076"

destring kreis_code, replace
replace Kreis = "Landkreis Mecklenburgische Seenplatte" if kreis_code==13071
replace Kreis = "Landkreis Rostock" if kreis_code==13072
replace Kreis = "Landkreis Vorpommern-Rügen" if kreis_code==13073
replace Kreis = "Landkreis Nordwestmecklenburg" if kreis_code==13074
replace Kreis= "Landkreis Vorpommern-Greifswald" if kreis_code==13075
replace Kreis= "Landkreis Ludwigslust-Parchim"  if kreis_code==13076
replace Kreis= "Kreisfreie Stadt Rostock, Hansestadt" if kreis_code==13003
replace Kreis= "Kreisfreie Stadt Schwerin, Landeshauptstadt"  if kreis_code==13004

collapse (sum) pop_tot pop_tot15_64, by (kreis_code Kreis year)
order kreis_code Kreis year

*Save population dataset
save "${tempdata}MV_reformed_counties_pop.dta", replace

*------------------------------------------------------------------------------*

clear

exit
